package com.sky.service.impl;

import com.sky.dto.GoodsSalesDTO;
import com.sky.entity.Orders;
import com.sky.mapper.OrderMapper;
import com.sky.mapper.UserMapper;
import com.sky.service.ReportService;
import com.sky.service.WorkspaceService;
import com.sky.vo.*;
import lombok.extern.slf4j.Slf4j;
import net.sf.jxls.transformer.XLSTransformer;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.jxls.common.Context;
import org.jxls.util.JxlsHelper;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import javax.servlet.http.HttpServletResponse;
import java.io.BufferedOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.net.URLEncoder;
import java.time.LocalDate;
import java.time.LocalDateTime;
import java.time.LocalTime;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import java.util.stream.IntStream;

/**
 * @author Administrator
 * @version 1.0
 * @description: 数据统计服务类
 * @date 2024/1/30 10:06
 */
@Slf4j
@Service
public class ReportServiceImpl implements ReportService {
    @Autowired
    private OrderMapper orderMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private WorkspaceService workspaceService;

    /**
     * 1、根据起始日期统计营业额
     *
     * @param begin
     * @param end
     * @return
     */
    @Override
    public TurnoverReportVO turnoverSattistics(LocalDate begin, LocalDate end) {
        // 1、查询获取起止日期之间的所有日期列表
        List<LocalDate> dateList = this.getDateList(begin, end);

        // 2、查询获取起止日期之间的所有营业额列表
        List<BigDecimal> amountList = this.getAmountList(dateList);

        // 3、将日期列表转为逗号分隔的字符串
        String dateStr = StringUtils.join(dateList, ",");
        dateStr = dateList.stream().map(date -> date.toString()).collect(Collectors.joining(","));

        // 4、将营业额列表转为逗号分隔的字符串
        String turnoverStr = StringUtils.join(amountList, ",");

        //5、封装Vo返回
        return TurnoverReportVO.builder().dateList(dateStr).turnoverList(turnoverStr).build();
    }

    /**
     * 2、根据起始日期统计用户数据（每日新增用户、每日存量用户）
     *
     * @param begin
     * @param end
     * @return
     */
    @Override
    public UserReportVO userStatistics(LocalDate begin, LocalDate end) {
        // 1、查询获取起止日期之间的所有日期列表
        List<LocalDate> dateList = this.getDateList(begin, end);

        // 2、查询获取起止日期之间的所有新增用户列表 + 3、查询获取起止日期之间的所有存量用户列表
        List<Integer> newUserList = new ArrayList<>(dateList.size());
        List<Integer> totalUserList = new ArrayList<>(dateList.size());
        fillTwoUserList(dateList, newUserList, totalUserList);

        // 4、将日期列表转为逗号分隔的字符串
        // 5、将新增用户列表转为逗号分隔的字符串
        // 6、将存量用户列表转为逗号分隔的字符串
        // 7、封装Vo返回
        return UserReportVO.builder()
                .dateList(StringUtils.join(dateList, ","))
                .newUserList(StringUtils.join(newUserList, ","))
                .totalUserList(StringUtils.join(totalUserList, ","))
                .build();
    }

    /**
     * 3、根据起始日期统计订单数据（每日订单数、每日有效订单数、订单总数、有效订单总数、订单完成率）
     *
     * @param begin
     * @param end
     * @return
     */
    @Override
    public OrderReportVO ordersStatistics(LocalDate begin, LocalDate end) {
        // 1、查询获取起止日期之间的所有日期列表
        List<LocalDate> dateList = this.getDateList(begin, end);

        // 2、填充订单列表和有效订单列表
        List<Integer> orderCountList = new ArrayList<>(dateList.size());
        List<Integer> validOrderCountList = new ArrayList<>(dateList.size());
        fillTwoOrderList(dateList, orderCountList, validOrderCountList);

        // 3、累加第2步的所有订单数，得到订单总数
        Integer totalOrderCount = orderCountList.stream().mapToInt(o -> o).sum();
        totalOrderCount = orderCountList.stream().flatMapToInt(o -> IntStream.of(o)).sum();
        totalOrderCount = orderCountList.stream().reduce(Integer::sum).get();
        totalOrderCount = orderCountList.stream().collect(Collectors.summingInt(o -> o));

        // 4、累加第3步的所有有效订单数，得到有效订单总数
        Integer validOrderCount = validOrderCountList.stream().mapToInt(o -> o).sum();

        // 5、有效订单总数 / 订单总数，得到订单完成率
        Double orderCompletionRate = totalOrderCount == 0 ? 0.0 : validOrderCount.doubleValue() / totalOrderCount;

        // 6、封装Vo返回
        return OrderReportVO.builder()
                .dateList(StringUtils.join(dateList, ","))
                .orderCountList(StringUtils.join(orderCountList, ","))
                .validOrderCountList(StringUtils.join(validOrderCountList, ","))
                .totalOrderCount(totalOrderCount)
                .validOrderCount(validOrderCount)
                .orderCompletionRate(orderCompletionRate)
                .build();
    }

    /**
     * 4、统计销量排名前十的商品（菜品 、 套餐）
     *
     * @param begin
     * @param end
     * @return
     */
    @Override
    public SalesTop10ReportVO top10(LocalDate begin, LocalDate end) {
        // 1、统计查询销量排前十的商品（商品名称 ： 销量）
        Map params = new HashMap();
        params.put("begin", begin);
        params.put("end", end);
        params.put("status", Orders.COMPLETED);
        List<GoodsSalesDTO> goodsSalesList = this.orderMapper.top10(params);

        // 2、从列表中提取商品名称字符串
        String nameListStr = goodsSalesList.stream().map(GoodsSalesDTO::getName).collect(Collectors.joining(","));

        // 3、从列表中提取销量字符串
        String numberListStr = goodsSalesList.stream().map(googs -> googs.getNumber().toString()).collect(Collectors.joining(","));

        // 4、封装Vo返回
        return SalesTop10ReportVO.builder().nameList(nameListStr).numberList(numberListStr).build();
    }

    /**
     * 近30天营业数据excel导出
     *
     * @param response
     */
    @Override
    public void export(HttpServletResponse response) throws IOException, InvalidFormatException {
        // 1、查询近30天的营业概览数据
        LocalDate begin = LocalDate.now().minusDays(30);
        LocalDate end = LocalDate.now().minusDays(1);
        BusinessDataVO businessData = workspaceService.getBusinessData(LocalDateTime.of(begin, LocalTime.MIN), LocalDateTime.of(end, LocalTime.MAX));

        exportStyle3(response, begin, end, businessData);

    }

    /**
     * 使用org.jxls进行基于excel模板的导出（模板根据批注读取配置）  poi  版本不能低于4.0
     * @param response
     * @param startDate
     * @param endDate
     * @param businessData
     * @throws IOException
     */
    private void exportStyle3(HttpServletResponse response, LocalDate startDate, LocalDate endDate, BusinessDataVO businessData) throws IOException {
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板3.xlsx");
        List<LocalDate> dateList = this.getDateList(startDate, endDate);
        List<BusinessDataVO> voList = new ArrayList<>();
        for (LocalDate date : dateList) {
            BusinessDataVO businessDataVO = this.workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            businessDataVO.setDate(date.toString());
            voList.add(businessDataVO);
        }
        businessData.setDate("时间：" + startDate + "至" + endDate);
        Context context = new Context();
        context.putVar("dataList" , voList);
        context.putVar("bd" , businessData);

        //5、写入到浏览器，关闭流
        response.setContentType("application/octet-stream");  ////非必须
        response.addHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode("运营数据报表1.xlsx", "UTF-8"));  //非必须
        JxlsHelper.getInstance().processTemplate(in, new BufferedOutputStream(response.getOutputStream()), context);
    }

    /**
     * 使用net.sf.jxls.jxls-core进行基于excel模板的导出(poi 版本不能高于4.0)
     *
     * @param response
     * @param startDate
     * @param endDate
     * @param businessData
     * @return
     * @throws InvalidFormatException
     */
    private void exportStyle2(HttpServletResponse response, LocalDate startDate, LocalDate endDate, BusinessDataVO businessData) throws InvalidFormatException, IOException {
        //1、准备数据
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板2.xlsx");
        List<LocalDate> dateList = this.getDateList(startDate, endDate);

        //1.1、准备明细数据List
        List<BusinessDataVO> voList = new ArrayList<>();
        for (LocalDate date : dateList) {
            BusinessDataVO businessDataVO = this.workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            businessDataVO.setDate(date.toString());
            voList.add(businessDataVO);
        }

        //使用net.sf.jxls.jxls-core进行基于excel模板的导出（模板根据表达式读取配置） poi 版本不能高于4.0
        Map<String, Object> map = new HashMap<>();
        map.put("dataList", voList);
        map.put("bd", businessData);

        Workbook excel = new XLSTransformer().transformXLS(in, map);
        excel.write(response.getOutputStream());
    }

    /**
     * 通过原生POI方式进行数据导出
     * @param response
     * @param begin
     * @param end
     * @param businessData
     * @throws IOException
     */
    private void exportStyle1(HttpServletResponse response, LocalDate begin, LocalDate end, BusinessDataVO businessData) throws IOException {
        // 2、向excel中写入数据
        // 2.1、获取excel模版对象
        InputStream in = this.getClass().getClassLoader().getResourceAsStream("template/运营数据报表模板.xlsx");
        XSSFWorkbook excel = new XSSFWorkbook(in);
        XSSFSheet sheet = excel.getSheetAt(0);
        // 2.2、写入日期信息
        sheet.getRow(1).getCell(1).setCellValue("日期：" + begin + "至" + end);

        // 2.3、写入营业概要数据
        XSSFRow row4 = sheet.getRow(3);
        row4.getCell(2).setCellValue(businessData.getTurnover());
        row4.getCell(4).setCellValue(businessData.getOrderCompletionRate());
        row4.getCell(6).setCellValue(businessData.getNewUsers());

        XSSFRow row5 = sheet.getRow(4);
        row5.getCell(2).setCellValue(businessData.getValidOrderCount());
        row5.getCell(4).setCellValue(businessData.getUnitPrice());

        // 2.4、写入营业明细数据
        List<LocalDate> dateList = this.getDateList(begin, end);
        int rowNum = 7;
        for (LocalDate date : dateList) {
            businessData = workspaceService.getBusinessData(LocalDateTime.of(date, LocalTime.MIN), LocalDateTime.of(date, LocalTime.MAX));
            XSSFRow row = sheet.getRow(rowNum++);
            row.getCell(1).setCellValue(date.toString());
            row.getCell(2).setCellValue(businessData.getTurnover());
            row.getCell(3).setCellValue(businessData.getValidOrderCount());
            row.getCell(4).setCellValue(businessData.getOrderCompletionRate());
            row.getCell(5).setCellValue(businessData.getUnitPrice());
            row.getCell(6).setCellValue(businessData.getNewUsers());
        }

        // 3、以流的形式向浏览器输出文件
        response.setContentType("application/vnd.ms-excel");
        excel.write(response.getOutputStream());

        // 4、关闭流
        in.close();
        excel.close();
    }

    private void fillTwoOrderList(List<LocalDate> dateList, List<Integer> orderCountList, List<Integer> validOrderCountList) {
        LocalDate begin = dateList.get(0), end = dateList.get(dateList.size() - 1);

        // 方式一: SQL 分组统计（弊端：需要查询两次数据库）
        // 2.1、分组查询日期范围内的订单数（日期：订单总数）
        /*Map params = new HashMap();
        params.put("begin", begin);
        params.put("end", end);
        List<Map> orderCountMapList = orderMapper.queryOrderCountByParamsWithGroup(params);
        Map<String, Integer> orderCountMap = orderCountMapList.stream().
                collect(Collectors.toMap(o -> (String) o.get("date"), o -> Integer.valueOf(o.get("num").toString())));

        // 2.2、分组查询日期范围内的有效订单数（日期：有效订单总数）

        params.put("status",Orders.COMPLETED);
        List<Map> validOrderCountMapList = orderMapper.queryOrderCountByParamsWithGroup(params);
        Map<String, Integer> validOrderCountMap = validOrderCountMapList.stream().
                collect(Collectors.toMap(o -> (String) o.get("date"), o -> Integer.valueOf(o.get("num").toString())));*/


        // 方式二：SQL普通查询一次，通过stream自己进行分组统计
        // 2.1、查询日期范围内的所有订单数据（订单时间、订单状态）
        Map params = new HashMap();
        params.put("begin", begin);
        params.put("end", end);
        List<Orders> orderList = orderMapper.queryOrdersByParams(params);

        // 2.2、获取总订单数Map(日期：订单总数)
        Map<String, Long> orderCountLongMap = orderList.stream().collect(Collectors.groupingBy(o -> o.getOrderTime().toLocalDate().toString(), Collectors.counting()));
        Map<String, Integer> orderCountMap = new HashMap<>(orderCountLongMap.size());
        orderCountLongMap.forEach((k, v) -> orderCountMap.put(k, Integer.valueOf(v.toString())));


        // 2.3、获取有效订单数Map(日期：订单总数)
        Map<String, Long> validOrderCountLongMap = orderList.stream().filter(o -> Orders.COMPLETED.equals(o.getStatus()))
                .collect(Collectors.groupingBy(o -> o.getOrderTime().toLocalDate().toString(), Collectors.counting()));
        Map<String, Integer> validOrderCountMap = new HashMap<>(orderCountLongMap.size());
        validOrderCountLongMap.forEach((k, v) -> validOrderCountMap.put(k, Integer.valueOf(v.toString())));


        // 3、循环日期列表，组装订单列表和有效订单列表数据
        for (LocalDate date : dateList) {
            Integer orderCount = orderCountMap.getOrDefault(date.toString(), 0);
            Integer validOrderCount = validOrderCountMap.getOrDefault(date.toString(), 0);

            orderCountList.add(orderCount);
            validOrderCountList.add(validOrderCount);
        }
    }

    /**
     * 统计填充新增用户集合和截止当天存量用户集合
     *
     * @param dateList      日期集合
     * @param newUserList   新增用户集合
     * @param totalUserList 存量用户集合
     */
    private void fillTwoUserList(List<LocalDate> dateList, List<Integer> newUserList, List<Integer> totalUserList) {
        LocalDate begin = dateList.get(0), end = dateList.get(dateList.size() - 1);
        // 方式一：逐天统计，效率不高
        //select count(*) from `user` where DATE_FORMAT(create_time,'%Y-%m-%d') = '2024-01-21'
        /*dateList.forEach(date -> {
            Map params = new HashMap();
            params.put("date",date);
            params.put("newUserFlag",true);
            Integer newUserCount = userMapper.countUserByParams(params);

            params.put("newUserFlag",false);
            Integer totalUserCount = userMapper.countUserByParams(params);

            newUserList.add(newUserCount);
            totalUserList.add(totalUserCount);
        });*/

        // 方式二：根据日期分组查询，一次查询，效率高
        // 3.1、根据日期分组查询新增用户并转化成map (日期 ： 新增用户数)
        Map params = new HashMap();
        params.put("begin", begin);
        params.put("end", end);
        List<Map> newUserCountMapList = orderMapper.queryUserCountByParamsWithGroup(params);
        Map<String, Integer> newUserCountMap = newUserCountMapList.stream()
                .collect(Collectors.toMap(u -> (String) u.get("date"), u -> Integer.valueOf(u.get("num").toString())));

        // 3.2、查询截止到统计开始日期前一天的存量用户数
        params = new HashMap();
        params.put("date", begin.minusDays(1));
        params.put("newUserFlag", false);
        Integer totalUserCount = userMapper.countUserByParams(params);

        // 3.3、遍历日期集合，从map中获取每天对应的新增用户数，将新增用户数添加到集合中
        for (LocalDate date : dateList) {
            Integer newUserCount = newUserCountMap.getOrDefault(date.toString(), 0);
            totalUserCount += newUserCount;

            newUserList.add(newUserCount);
            totalUserList.add(totalUserCount);
        }
    }

    /**
     * 根据日期范围获取营业额列表
     *
     * @param dateList
     * @return
     */
    private List<BigDecimal> getAmountList(List<LocalDate> dateList) {
        LocalDate begin = dateList.get(0), end = dateList.get(dateList.size() - 1);

        // 方式一：根据日期逐天查询，会发起多次查询，效率不高
        /*List<Double> amountList = new ArrayList<>(dateList.size());
        // select sum(amount) from orders where status = 5 and date_format(order_time,'%Y-%m-%d') = '2024-01-28'
        dateList.forEach(date -> {
            Map params = new HashMap();
            params.put("date",date);
            params.put("status", Orders.COMPLETED);
            Double totalAmount = this.orderMapper.sumAmountByParams(params);
            totalAmount = ObjectUtil.isEmpty(totalAmount) ? 0.0 : totalAmount;
            amountList.add(totalAmount);
        });*/

        // 方式二：分组统计查询
        // 2.1、根据日期分组查询营业额并转化为map (日期 ：营业额)
        Map params = new HashMap();
        params.put("begin", begin);
        params.put("end", end);
        params.put("status", Orders.COMPLETED);
        List<Map> amountMaptList = this.orderMapper.queryByParamsWithGroup(params);
        Map<String, BigDecimal> amountMap = amountMaptList.stream().collect(Collectors.toMap(a -> (String) a.get("date"), a -> (BigDecimal) a.get("amount")));

        // 2.2、循环日期列表，根据上面的营业额map组装营业额列表数据
        List<BigDecimal> amountList = new ArrayList<>(dateList.size());
        for (LocalDate date : dateList) {
            BigDecimal amount = amountMap.getOrDefault(date.toString(), BigDecimal.ZERO);
            amountList.add(amount);
        }
        return amountList;
    }

    /**
     * 根据起止日期之间的所有日期列表
     *
     * @param begin
     * @param end
     * @return
     */
    private List<LocalDate> getDateList(LocalDate begin, LocalDate end) {
        List<LocalDate> dateList = new ArrayList<>();
        // 方式一
        /*while(begin.isBefore(end)){
            dateList.add(begin);
            begin = begin.plusDays(1);
        }
        dateList.add(begin);*/

        // 方式二
        for (LocalDate date = begin; date.isBefore(end); date = date.plusDays(1)) {
            dateList.add(date);
        }
        dateList.add(end);
        return dateList;
    }

    //TODO
    public static void main(String[] args) {
        /*List<LocalDate> dateList = getDateList(LocalDate.parse("2024-01-01"), LocalDate.parse("2024-01-10"));
        System.out.println(StringUtils.join(dateList, ","));*/

        List<Orders> list = new ArrayList<>();
        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-01T18:30:00")).build());
        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-01T18:30:00")).build());
        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-01T18:30:00")).build());

        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-02T18:30:00")).build());
        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-02T18:30:00")).build());

        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-03T18:30:00")).build());
        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-03T18:30:00")).build());
        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-03T18:30:00")).build());
        list.add(Orders.builder().orderTime(LocalDateTime.parse("2024-01-03T18:30:00")).build());

        Map<String, Long> collect = list.stream().collect(Collectors.groupingBy(o -> o.getOrderTime().toLocalDate().toString(), Collectors.counting()));
        collect.forEach((k, v) -> System.out.println(k + " : " + v));
    }

}
